﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.ReportService.Examination
{
    public class GetExamByIdDbCommand : ICommand<ExaminationDTO>
    {
        private readonly int _examId;
        private const string _sql = @"
              SELECT e.*, t.TermName FROM dbo.Examination e INNER JOIN dbo.Term t ON t.TermId = e.TermId
             WHERE ExaminationId = @ExamId

             SELECT e.*, a.Content as AnswerDetail FROM dbo.ExamDetail e INNER JOIN
                                dbo.Answer a ON a.AnswerId = e.AnswerId 
                    WHERE ExamID = @ExamId
        ";

        public GetExamByIdDbCommand(int examId)
        {
            _examId = examId;
        }

        public ExaminationDTO Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (var connection = new SqlConnection(connectionString))
            {
                var cmd = new SqlCommand(_sql, connection);
                cmd.Parameters.Add("@ExamId", SqlDbType.Int).Value = _examId;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    var dto = GetDto(reader);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        dto.Details.Add(GetDetailDto(reader));
                    }
                    return dto;
                }
                finally
                {
                    reader.Close();
                }
               
            }
            return null;
        }

        private ExaminationDTO GetDto(SqlDataReader reader)
        {
            var dto = new ExaminationDTO()
            {
                ExaminationId = (int)reader["ExaminationId"],
                CreateDate = Convert.ToDateTime(reader["CreateDate"]).ToString("dd-MM-yyyy HH:mm:ss"),
                TermName = reader["TermName"].ToString(),
                Score = (int)reader["Score"],
                UserId = (int)reader["UserId"],
                TermId = (int)reader["TermId"],
                ExamDate = Convert.ToDateTime(reader["CreateDate"]).ToString("yyyy-MM-dd")
            };
            dto.Details = new List<ExamDetailDTO>();
            return dto;
        }

        private ExamDetailDTO GetDetailDto(SqlDataReader reader)
        {
            var dto = new ExamDetailDTO()
            {
                ExamDetailId = (int)reader["ExamDetailId"],
                QuestionId = (int)reader["QuestionId"],
                AnswerId = (int)reader["AnswerId"],
                AnswerDetail = reader["AnswerDetail"].ToString()
            };
            return dto;
        }


       

    }
}
